package DAO;

import Logica.Owner;
import Logica.Pen;
import Logica.Staff;
import Logica.Supplies;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

import java.util.ArrayList;

public class SupplieDao {
    public SupplieDao() {
    }


    public boolean creaSupplie(int ClinicNumber,String tipoS, String nameS,
                               String description, int cantidad, int reorderl,
                               int reorderq, float cost) {

        int numberS = -5;
        Conexion conexion = new Conexion();
        boolean creado = false;
        Connection conex;

        try {
            conex = conexion.getConnection();
            PreparedStatement ps = null;
            PreparedStatement ps2 = null;
            ResultSet rs = null;

            ResultSet rsa = null;
            int mayor = -1;
            ps =
            conex.prepareStatement("select max (itemNumber)  from supplies");

            rsa = ps.executeQuery();
            if (rsa != null) {
                while (rsa.next()) {
                    mayor = rsa.getInt(1);
                }
            } else {
                numberS = 1;
            }
            ps =
 conex.prepareStatement("  INSERT INTO supplies (itemNumber, typesu,name,quantity,description,reorderLVL,reorderQuantity,Costs,ClinicNumber,Replacement) VALUES (?, ?,?,?,?,?,?,?,?,?)");

            numberS = mayor + 1;
            ps.setInt(1, numberS);
            ps.setString(2, tipoS);
            ps.setString(3, nameS);
            ps.setInt(4, cantidad);
            ps.setString(5, description);
            ps.setInt(6, reorderl);
            ps.setInt(7, reorderq);
            ps.setFloat(8, cost);
            ps.setInt(9, ClinicNumber);
            //Saco como remplazo el anterior,si solo existe tal elemennto colocamos -1, no tiene cambio.
            if (numberS == 1)
                ps.setNull(10, java.sql.Types.INTEGER);
            else
                ps.setInt(10, numberS - 1);


            int numeroFilas = ps.executeUpdate();
            System.out.println("El numero de filas que inserto fueron" +
                               numeroFilas);
            creado = true;

           conex.close();
           
        } catch (SQLException ex) {
            System.err.println("SQLException: " + ex.getMessage());

        }
        return creado;
    }
    
  public boolean eliminarSupplie(int numberS) {

    Conexion conexion = new Conexion();
    boolean eliminado = false;
    Connection conex;

    try {
      conex = conexion.getConnection();
      PreparedStatement ps = null;
      ResultSet rs = null;

      ps = conex.prepareStatement("DELETE FROM supplies where itemnumber = ?");
      ps.setInt(1, numberS);

      int numeroFilas = ps.executeUpdate();
      System.out.println("El numero de filas que borro fueron" + numeroFilas);
      eliminado = true;
      conex.close();

    } catch (SQLException ex) {
      System.err.println("SQLException: " + ex.getMessage());

    }
    return eliminado;
  }


//Si se cambia un NoPharma debe recibir obligatoriamente la dosis y el Metadmon para poder Crearlo.
     public boolean modificaSupplieTipo(int numberS,String dosis,String MetAdmon) {
        boolean EliminarPharm=false;
        Conexion conexion = new Conexion();
        Connection conex;
        try {
            conex = conexion.getConnection();
            PreparedStatement ps = null;
            ResultSet rs=null;
            
            String cambio="";
           
            ps =
            conex.prepareStatement("SELECT typesu from supplies where itemNumber = ?");
              ps.setInt(1,numberS);
            rs=ps.executeQuery();
                       
            if (rs.next()){
                cambio=rs.getString(1);
                }
            
            if (cambio.equalsIgnoreCase("")) return false;
            
            ps =
 conex.prepareStatement("UPDATE supplies set typesu = ?  where itemNumber = ?");

            if (cambio.equals("Pharma")){
            ps.setString(1,"NoPharma" );
            EliminarPharm=true;
            }
            else
            {
            ps.setString(1,"Pharma" );   
            }    
            ps.setInt(2,numberS );

            int numeroFilas = ps.executeUpdate();
            System.out.println("El numero de filas que actualizo fueron" +
                               numeroFilas);
            conex.close();
            
            PharmaDao pd=new PharmaDao();
            if (EliminarPharm){
             pd.eliminarPharma(numberS);
            }
            else {
                if (!dosis.equals("") && !MetAdmon.equals(""))
                pd.creaPharmaceutical(numberS,dosis,MetAdmon);   
                else{
                    System.out.println("No Especifico dosis y Metodo Administracion, aparece Standar.");
                        pd.creaPharmaceutical(numberS,"Diaria","Oral");
                    }
                }
        } catch (SQLException ex) {
            System.err.println("SQLException: " + ex.getMessage());
            return false;
        }
        return true;
    }
     
    public boolean modificaSupplieName(int numberS,  String name)
    {
      Conexion conexion = new Conexion();
      Connection conex;
      try {
        conex = conexion.getConnection();
        PreparedStatement ps = null;
        
        ps =
            conex.prepareStatement("UPDATE supplies set name = ?  where itemNumber = ?"); 
        
        ps.setString(1, name);
        ps.setInt(2, numberS);
        
        int numeroFilas = ps.executeUpdate();
        System.out.println("El numero de filas que actualizo fueron" +
                           numeroFilas);

        conex.close();
        
      } catch (SQLException ex) {
        System.err.println("SQLException: " + ex.getMessage());
        return false;
      }
      return true;
    }
    
    public boolean modificaSupplieQuantity(int numberS,  int quan)
    {
      Conexion conexion = new Conexion();
      Connection conex;
      try {
        conex = conexion.getConnection();
        PreparedStatement ps = null;
        
        ps =
            conex.prepareStatement("UPDATE supplies set QUANTITY = ?  where itemNumber = ?"); 
        
        ps.setInt(1, quan);
        ps.setInt(2, numberS);
        
        int numeroFilas = ps.executeUpdate();
        System.out.println("El numero de filas que actualizo fueron" +
                           numeroFilas);

        conex.close();
        
      } catch (SQLException ex) {
        System.err.println("SQLException: " + ex.getMessage());
        return false;
      }
      return true;
    }
    
    public boolean modificaSupplieDescription(int numberS,  String des)
    {
      Conexion conexion = new Conexion();
      Connection conex;
      try {
        conex = conexion.getConnection();
        PreparedStatement ps = null;
        
        ps =
            conex.prepareStatement("UPDATE supplies set DESCRIPTION = ?  where itemNumber = ?"); 
        
        ps.setString(1, des);
        ps.setInt(2, numberS);
        
        int numeroFilas = ps.executeUpdate();
        System.out.println("El numero de filas que actualizo fueron" +
                           numeroFilas);

        conex.close();
        
      } catch (SQLException ex) {
        System.err.println("SQLException: " + ex.getMessage());
        return false;
      }
      return true;
    }
    
    public boolean modificaSupplieReorderLvl(int numberS,  int rl)
    {
      Conexion conexion = new Conexion();
      Connection conex;
      try {
        conex = conexion.getConnection();
        PreparedStatement ps = null;
        
        ps =
            conex.prepareStatement("UPDATE supplies set REORDERlvl = ?  where itemNumber = ?"); 
        
        ps.setInt(1, rl);
        ps.setInt(2, numberS);
        
        int numeroFilas = ps.executeUpdate();
        System.out.println("El numero de filas que actualizo fueron" +
                           numeroFilas);

        conex.close();
        
      } catch (SQLException ex) {
        System.err.println("SQLException: " + ex.getMessage());
        return false;
      }
      return true;
    }
    
    public boolean modificaSupplieReorderQuan(int numberS,  int quan)
    {
      Conexion conexion = new Conexion();
      Connection conex;
      try {
        conex = conexion.getConnection();
        PreparedStatement ps = null;
        
        ps =
            conex.prepareStatement("UPDATE supplies set REORDERquantity = ?  where itemNumber = ?"); 
        
        ps.setInt(1, quan);
        ps.setInt(2, numberS);
        
        int numeroFilas = ps.executeUpdate();
        System.out.println("El numero de filas que actualizo fueron" +
                           numeroFilas);

        conex.close();
        
      } catch (SQLException ex) {
        System.err.println("SQLException: " + ex.getMessage());
        return false;
      }
      return true;
    }
    
    
    public boolean modificaSupplieCost(int numberS,  float cost)
    {
      Conexion conexion = new Conexion();
      Connection conex;
      try {
        conex = conexion.getConnection();
        PreparedStatement ps = null;
        
        ps =
            conex.prepareStatement("UPDATE supplies set COSTS = ?  where itemNumber = ?"); 
        
        ps.setFloat(1, cost);
        ps.setInt(2, numberS);
        
        int numeroFilas = ps.executeUpdate();
        System.out.println("El numero de filas que actualizo fueron" +
                           numeroFilas);

        conex.close();
        
      } catch (SQLException ex) {
        System.err.println("SQLException: " + ex.getMessage());
        return false;
      }
      return true;
    }
    
    public boolean modificaSupplieReplacement(int numberS,  int rep)
    {
      Conexion conexion = new Conexion();
      Connection conex;
      try {
        conex = conexion.getConnection();
        PreparedStatement ps = null;
        
        ps =
            conex.prepareStatement("UPDATE supplies set REPLACEMENT = ?  where itemNumber = ?"); 
        
        ps.setInt(1, rep);
        ps.setInt(2, numberS);
        
        int numeroFilas = ps.executeUpdate();
        System.out.println("El numero de filas que actualizo fueron" +
                           numeroFilas);

        conex.close();
        
      } catch (SQLException ex) {
        System.err.println("SQLException: " + ex.getMessage());
        return false;
      }
      return true;
    }
    

  public boolean modificaSupplieClinic(int NumSup1,  int Nocli)
  {
    Conexion conexion = new Conexion();
    Connection conex;
    try {
      conex = conexion.getConnection();
      PreparedStatement ps = null;
      
      ps =
          conex.prepareStatement("UPDATE supplies set clinicnumber = ?  where itemNumber = ?"); 
      
      ps.setInt(1, Nocli);
      ps.setInt(2, NumSup1);
      
      int numeroFilas = ps.executeUpdate();
      System.out.println("El numero de filas que actualizo fueron" +
                         numeroFilas);

      conex.close();
      
    } catch (SQLException ex) {
      System.err.println("SQLException: " + ex.getMessage());
      return false;
    }
    return true;
  }

  public Object[][] DatosSupplieTabla() {
    Conexion conexion = new Conexion();
    Connection conex;
    Object[][] datos = new Object[100][100];

    try {
      conex = conexion.getConnection();
      PreparedStatement ps = null;
      ResultSet rs = null;

      ps = conex.prepareStatement("select * FROM supplies ");     
      int i = 0;
      int j = 0;

      rs = ps.executeQuery();
      System.out.println(rs + " es el resultado de la consulta !");
      datos[i][j]= "ITEM NUMBER";
      j++;
      datos[i][j]="TYPESUP";
      j++;
      datos[i][j]="NAME";
      j++;
      datos[i][j]="QUANTITY";
      j++;
      datos[i][j]="DESCRIPTION";
      j++;
      datos[i][j]= "REORDER LEVEL";
      j++;
      datos[i][j]="REORDER QUANTITY";
      j++;
      datos[i][j]="COST";
      j++;
      datos[i][j]="CLINIC NUMBER";
      j++;
      datos[i][j]="REPLACEMENT";
      i++;
      j=0;
      while (rs.next()) {

        datos[i][j] = rs.getInt(1);
        System.out.print(datos[i][j] + " ");
        j++;
        datos[i][j] = rs.getString(2);
        System.out.print(datos[i][j] + " ");
        j++;
        datos[i][j] = rs.getString(3);
        System.out.print(datos[i][j] + " ");
        j++;
        datos[i][j] = rs.getInt(4);
        System.out.print(datos[i][j] + " ");
        j++;
        datos[i][j] = rs.getString(5);
        System.out.print(datos[i][j] + " ");
        j++;
        datos[i][j] = rs.getInt(6);
        System.out.print(datos[i][j] + " ");
        j++;
        datos[i][j] = rs.getInt(7);
        System.out.print(datos[i][j] + " ");
        j++;
        datos[i][j] = rs.getFloat(8);
        System.out.print(datos[i][j] + " ");
        datos[i][j] = rs.getInt(9);
        System.out.print(datos[i][j] + " ");
        j++;
        datos[i][j] = rs.getInt(10);
        System.out.print(datos[i][j] + " ");
        i++;
        j=0;
      }
      
      rs.close();
      ps.close();
      conex.close();
      
    } catch (SQLException ex) {
      System.err.println("SQLException: " + ex.getMessage());
    }
    return datos;
  }
    
}


